﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Transactions;
using System.Data.Common;

using ECS.DAL.Entity;

namespace ECS.DAL.Component
{
    public class BaseSQLModel
    {
        /// <summary>
        /// transaction 
        /// </summary>
        /// <returns></returns>
        public static TransactionScope CreateTransactionScope()
        {
            var transactionOptions = new TransactionOptions();
            transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
            transactionOptions.Timeout = TransactionManager.MaximumTimeout;
            return new TransactionScope(TransactionScopeOption.Suppress, transactionOptions);
        }
        public static IEnumerable<List<T>> Chunk<T>(List<T> l, int chunkSize)
        {
            if (chunkSize < 0)
            {
                throw new ArgumentException("chunkSize cannot be negative", "chunkSize");
            }
            for (int i = 0; i < l.Count; i += chunkSize)
            {
                yield return l.Skip(i).Take(chunkSize).ToList();
            }
        }
        public static string GetListSqlWithQuote(List<string> list)
        {
            return String.Join(", ", list.Select(x => "'" + EscapeSqlString(x) + "'"));
        }
        public static string GetListSqlWithQuote(List<decimal> list)
        {
            return GetListSqlWithQuote(list.Select(x => x.ToString()).ToList());
        }
        public static string GetListSqlNoQuote(List<string> list)
        {
            return String.Join(", ", list.Select(x => x));
        }
        public static string GetListSqlNoQuote(List<decimal> list)
        {
            return GetListSqlNoQuote(list.Select(x => x.ToString()).ToList());
        }
        public static string EscapeSqlString(string s)
        {
            return s.Replace("'", "''").Replace("\\", "\\\\");
        }
        public static string SqlWithQoute(string s)
        {
            return string.Format("'{0}'", s);
        }
        public static string SqlWithoutQoute(object s)
        {
            return string.Format("{0}", s);
        }
        /// <summary>
        /// check field name with specific index in datarecord
        /// </summary>
        /// <param name="r"></param>
        /// <param name="i"></param>
        /// <param name="name"></param>
        protected void CheckName(IDataRecord r, int i, string name)
        {
            //if (!CheckNameOnLoad) return;
            if (name == null) return;
            var got = r.GetName(i);
            if (r.GetName(i) != name)
                throw new Exception(
                    String.Format(
                      "Unmatch field name on load (expect \"{0}\", got \"{1}\")",
                      name, got));
        }

        protected string GetString(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return null;
            }
            else
            {
                return reader.GetString(index);
            }
        }
        public short? GetShort(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index))
            {
                return null;
            }
            else
            {
                return reader.GetInt16(index);
            }
        }
        protected int? GetInt(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return null;
            }
            else
            {
                return (int?)reader.GetInt32(index);
            }
        }
        protected long? GetLong(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return null;
            }
            else
            {
                return (long?)reader.GetInt64(index);
            }
        }
        protected float? GetFloat(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return null;
            }
            else
            {
                return  (float?)reader.GetFloat(index);
            }
        }

        protected double? GetDouble(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return null;
            }
            else
            {
                return (double?)reader.GetDouble(index);
            }
        }

        protected bool GetBoolean(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return false;
            }
            else
            {
                return reader.GetBoolean(index);
            }
        }

        protected DateTime? GetDate(int index, System.Data.IDataRecord reader, string fieldName = null)
        {
            CheckName(reader, index, fieldName);
            if (reader.IsDBNull(index)) 
            {
                return null;
            }
            else
            {
                return (DateTime?)reader.GetDateTime(index);
            }
        }
        /// <summary>
        /// Insert new record(s) 
        /// </summary>
        /// <returns></returns>
        public virtual void Insert(ObjectContext param)
        { }
        /// <summary>
        /// update record(s)
        /// </summary>
        /// <returns></returns>
        public virtual void Update(ObjectContext param)
        { }
        /// <summary>
        /// delete record(s)
        /// </summary>
        /// <returns></returns>
        public virtual void Delete(ObjectContext param)
        { }
        /// <summary>
        /// Save change for insert, update, delete by ObjectContext state
        /// </summary>
        /// <param name="item"></param>
        /// <returns></returns>
        public int SaveChanges(ObjectContext item)
        {
            switch (item.GetObjectSate())
            {
                case OBJECT_STATE.Insert:
                    Insert(item);
                    break;
                case OBJECT_STATE.Update:
                    Update(item);
                    break;
                case OBJECT_STATE.Delete:
                    Delete(item);
                    break;
                default:
                    break;
            }
            
            return 0;
        }
        /// <summary>
        /// generate Insert SQL command
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public virtual string GetInsertSQL(ObjectContext param)
        {
            return string.Empty;
        }
        /// <summary>
        /// generate update SQL command
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public virtual string GetUpdateSQL(ObjectContext param)
        {
            return string.Empty;
        }
        /// <summary>
        /// generate delete SQL command
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public virtual string GetDeleteSQL(ObjectContext param)
        {
            return string.Empty;
        }
        /// <summary>
        /// Save change of multiple object context by evaluate object state
        /// </summary>
        /// <param name="items"></param>
        public virtual void SaveChanges(List<ObjectContext> items)
        {
            string sqlCmd = string.Empty;
            string textMessage = string.Empty;
            using (System.Transactions.TransactionScope transaction = BaseSQLModel.CreateTransactionScope())
            {
                using (Entities entities = new Entities())
                {
                    entities.Open();
                    foreach (ObjectContext item in items)
                    {
                        switch (item.GetObjectSate())
                        {
                            case OBJECT_STATE.Insert:
                                sqlCmd = GetInsertSQL(item);
                                textMessage = "Insert ";
                                break;
                            case OBJECT_STATE.Update:
                                sqlCmd = GetUpdateSQL(item);
                                textMessage = "Update ";
                                break;
                            case OBJECT_STATE.Delete:
                                sqlCmd = GetDeleteSQL(item);
                                textMessage = "Delete ";
                                break;
                            default:
                                sqlCmd = string.Empty;
                                break;
                        }
                        if (!string.IsNullOrEmpty(sqlCmd))
                        {
                            DbCommand cmd = entities.CreateCommand(sqlCmd);
                            //cmd.CommandType = CommandType.Text;
                            //entities.SetCommand(cmd);
                            //int m_iRecordChanged = entities.SaveChanges();
                            int m_iRecordChanged = cmd.ExecuteNonQuery();
                            //if (m_iRecordChanged < 0)
                            //{
                            //    throw new Exception(string.Format("{0} transaction failed", textMessage));
                            //}
                            item.NotChangeObject();
                        }
                        
                    }
                }
                transaction.Complete();
            }
        }
    }
}
